import io
import os
import time

import pandas as pd


def Insert_sql(file_name):
    # 项目路径
    BASE_DIR = os.path.dirname(os.path.abspath('base_dir'))
    # 静态文件夹的路径,项目路径拼接向下的文件夹
    STATIC_DIR = os.path.join(BASE_DIR, 'static')
    excels_path = os.path.join(STATIC_DIR, 'excels')
    sqls_path = os.path.join(STATIC_DIR, 'sqls')

    # 配置字符串模板
    colum_name = """%s,tmfpe(%s) as %s,"""
    colum_name_tm = """%s,%s,"""
    colum_name_tm2 = """%s,%s"""
    colum_name2 = """%s,tmfpe(%s) as %s"""
    insert_sql = """INSERT OVERWRITE TABLE ZJSJTQ_GXQ.%s PARTITION() SELECT %s FROM %s.%s ;"""

    # 定义嵌套字符串模板行
    lines = ''

    # 读取文件
    file_loc = excels_path + "\\" + file_name
    df = pd.read_excel(file_loc, index_col=None, header=0)

    # 时间戳
    # times = datetime.datetime.now().strftime('%Y-%m-%d_%H-%M-%S')
    times = str(int(time.mktime(time.localtime())))

    # 插入数据文件
    ins = io.open(sqls_path + "\\insert_sql_" + times + ".txt", 'a',
                  encoding='utf-8')
    ins2 = io.open(sqls_path + "\\insert_sql_" + times + ".sql", 'a', encoding='utf-8')

    ins_txt = io.open(sqls_path + "\\insert_sql.txt", 'a', encoding='utf-8')

    # 保存txt文件名
    txt_name = 'insert_sql_' + times + ".txt"
    # 保存文件名
    sql_name = 'insert_sql_' + times + '.sql'

    # 第一次处理,生成基本insert语句
    for i in range(0, len(df)):
        print('插入表第' + str(i) + '行(初始)')
        if i < len(df) - 1:
            if df[i:i + 1]["物理表名称"].values[0] == df[i + 1:i + 2]["物理表名称"].values[0]:
                line = ''
                if df[i:i + 1]["是否分区字段"].values[0] != '是':
                    line = colum_name_tm % (df["字段名称"][i], df["脱敏后字段"][i])
                else:
                    # 分区字段跳过
                    pass
                lines += line
            else:
                # 最后一行
                if df[i:i + 1]["是否分区字段"].values[0] != '是':
                    line = colum_name_tm2 % (df["字段名称"][i], df["脱敏后字段"][i])
                    lines += line
                else:
                    # 分区字段跳过
                    pass
                final_create_sql = ''
                final_create_sql = insert_sql % (df["物理表名称"][i], lines, df["项目"][i], df["物理表名称"][i])
                lines = ''
                final_create_sql2 = final_create_sql.replace(""", FROM""", """ FROM""")
                ins.write(final_create_sql2 + "\n")
        else:
            # 文件最后一行
            if df[i:i + 1]["是否分区字段"].values[0] != '是':
                line = colum_name_tm2 % (df["字段名称"][i], df["脱敏后字段"][i])
                lines += line
            else:
                # 分区字段跳过
                pass
            final_create_sql = ''
            final_create_sql = insert_sql % (df["物理表名称"][i], lines, df["项目"][i], df["物理表名称"][i])
            lines = ''
            final_create_sql2 = final_create_sql.replace(""", FROM""", """ FROM""")
            ins.write(final_create_sql2 + "\n")
    ins.close()

    # 第二次处理,根据Sheet2修改insert语句
    txt_loc = sqls_path + "\\" + txt_name
    df = pd.read_excel(file_loc, index_col=None, header=0, sheet_name='Sheet2')
    with open(txt_loc, "r") as f:
        for line in f.readlines():
            pos1 = line.find(".")
            pos2 = line.find(" PARTITION()")
            print("初始位置:" + str(pos1 + 1) + ",结束位置:" + str(pos2) + ',表名:' + line[pos1 + 1:pos2])
            for i in range(0, len(df)):
                if line[pos1 + 1:pos2] == df[i:i + 1]["物理表名称"].values[0]:
                    pos3 = line.find("(")
                    pos4 = line.find(")")
                    # 判断是否为String类型,空则不是
                    if isinstance(df[i:i + 1]["分区信息"].values[0], str):
                        sql = line[0:pos3 + 1] + str(df[i:i + 1]["分区信息"].values[0]) + line[pos4:-2] + "WHERE " + str(
                            df[i:i + 1]["分区信息"].values[0]).replace(',', " AND ") + ";"
                    else:
                        sql = line[0:pos2 + 1] + line[pos4 + 2:-1]

                    ins2.write(sql + "\n")
                    ins_txt.write(sql + "\n")
    ins2.close()
    ins_txt.close()
    os.remove(txt_loc)
    return sql_name
